--    Program: 		CAPARIMP_CUSTOMER_PROFILE.sql
--    Description:  Import AR_CUSTOMER_PROFILE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_cust_account_profile_id	number;
                    		                 		
   cursor cur_ar_customer_profile is
      select * from CRST_HZ_CUSTOMER_PROFILES
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_profile loop   	
     l_err_msg_tmp := '';
     
     SELECT HZ_CUSTOMER_PROFILES_S.NEXTVAL 
     INTO l_cust_account_profile_id
     FROM DUAL;
     
     update crst_hz_customer_profiles
     set    cust_account_profile_id_n = l_cust_account_profile_id
     where  cust_account_profile_id = cur.CUST_ACCOUNT_PROFILE_ID;
     
     begin
       -- import data
       insert into HZ_CUSTOMER_PROFILES
       (
              CUST_ACCOUNT_PROFILE_ID        ,                           
              LAST_UPDATED_BY                ,                           
              LAST_UPDATE_DATE               ,                           
              LAST_UPDATE_LOGIN              ,                           
              CREATED_BY                     ,                           
              CREATION_DATE                  ,                           
              CUST_ACCOUNT_ID                ,                           
              STATUS                         ,                           
              COLLECTOR_ID                   ,                           
              CREDIT_ANALYST_ID              ,                           
              CREDIT_CHECKING                ,                           
              NEXT_CREDIT_REVIEW_DATE        ,                           
              TOLERANCE                      ,                           
              DISCOUNT_TERMS                 ,                           
              DUNNING_LETTERS                ,                           
              INTEREST_CHARGES               ,                           
              PREF_FUNCTIONAL_CURRENCY       ,                           
              SEND_STATEMENTS                ,                           
              CREDIT_BALANCE_STATEMENTS      ,                           
              CREDIT_HOLD                    ,                           
              PROFILE_CLASS_ID               ,                           
              SITE_USE_ID                    ,                           
              CREDIT_RATING                  ,                           
              RISK_CODE                      ,                           
              STANDARD_TERMS                 ,                           
              OVERRIDE_TERMS                 ,                           
              DUNNING_LETTER_SET_ID          ,                           
              INTEREST_PERIOD_DAYS           ,                           
              PAYMENT_GRACE_DAYS             ,                           
              DISCOUNT_GRACE_DAYS            ,                           
              STATEMENT_CYCLE_ID             ,                           
              ACCOUNT_STATUS                 ,                           
              PERCENT_COLLECTABLE            ,                           
              AUTOCASH_HIERARCHY_ID          ,                           
              ATTRIBUTE_CATEGORY             ,                           
              ATTRIBUTE1                     ,                           
              ATTRIBUTE2                     ,                           
              ATTRIBUTE3                     ,                           
              ATTRIBUTE4                     ,                           
              ATTRIBUTE5                     ,                           
              ATTRIBUTE6                     ,                           
              ATTRIBUTE7                     ,                           
              ATTRIBUTE8                     ,                           
              ATTRIBUTE9                     ,                           
              ATTRIBUTE10                    ,                           
              PROGRAM_APPLICATION_ID         ,                           
              PROGRAM_ID                     ,                           
              PROGRAM_UPDATE_DATE            ,                           
              REQUEST_ID                     ,                           
              WH_UPDATE_DATE                 ,                           
              ATTRIBUTE11                    ,                           
              ATTRIBUTE12                    ,                           
              ATTRIBUTE13                    ,                           
              ATTRIBUTE14                    ,                           
              ATTRIBUTE15                    ,                           
              AUTO_REC_INCL_DISPUTED_FLAG    ,                           
              TAX_PRINTING_OPTION            ,                           
              CHARGE_ON_FINANCE_CHARGE_FLAG  ,                           
              GROUPING_RULE_ID               ,                           
              CLEARING_DAYS                  ,                           
              JGZZ_ATTRIBUTE_CATEGORY        ,                           
              JGZZ_ATTRIBUTE1                ,                           
              JGZZ_ATTRIBUTE2                ,                           
              JGZZ_ATTRIBUTE3                ,                           
              JGZZ_ATTRIBUTE4                ,                           
              JGZZ_ATTRIBUTE5                ,                           
              JGZZ_ATTRIBUTE6                ,                           
              JGZZ_ATTRIBUTE7                ,                           
              JGZZ_ATTRIBUTE8                ,                           
              JGZZ_ATTRIBUTE9                ,                           
              JGZZ_ATTRIBUTE10               ,                           
              JGZZ_ATTRIBUTE11               ,                           
              JGZZ_ATTRIBUTE12               ,                           
              JGZZ_ATTRIBUTE13               ,                           
              JGZZ_ATTRIBUTE14               ,                           
              JGZZ_ATTRIBUTE15               ,                           
              GLOBAL_ATTRIBUTE1              ,                           
              GLOBAL_ATTRIBUTE2              ,                           
              GLOBAL_ATTRIBUTE3              ,                           
              GLOBAL_ATTRIBUTE4              ,                           
              GLOBAL_ATTRIBUTE5              ,                           
              GLOBAL_ATTRIBUTE6              ,                           
              GLOBAL_ATTRIBUTE7              ,                           
              GLOBAL_ATTRIBUTE8              ,                           
              GLOBAL_ATTRIBUTE9              ,                           
              GLOBAL_ATTRIBUTE10             ,                           
              GLOBAL_ATTRIBUTE11             ,                           
              GLOBAL_ATTRIBUTE12             ,                           
              GLOBAL_ATTRIBUTE13             ,                           
              GLOBAL_ATTRIBUTE14             ,                           
              GLOBAL_ATTRIBUTE15             ,                           
              GLOBAL_ATTRIBUTE16             ,                           
              GLOBAL_ATTRIBUTE17             ,                           
              GLOBAL_ATTRIBUTE18             ,                           
              GLOBAL_ATTRIBUTE19             ,                           
              GLOBAL_ATTRIBUTE20             ,                           
              GLOBAL_ATTRIBUTE_CATEGORY      ,                           
              CONS_INV_FLAG                  ,                           
              CONS_INV_TYPE                  ,                           
              AUTOCASH_HIERARCHY_ID_FOR_ADR  ,                           
              LOCKBOX_MATCHING_OPTION        ,                           
              OBJECT_VERSION_NUMBER          ,                           
              CREATED_BY_MODULE              ,                           
              APPLICATION_ID                 ,                           
              REVIEW_CYCLE                   ,                           
              LAST_CREDIT_REVIEW_DATE        ,                           
              PARTY_ID                       ,                           
              CREDIT_CLASSIFICATION                                      
       )
       values
       (
            l_cust_account_profile_id          ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_DATE               ,
            cur.LAST_UPDATE_LOGIN              ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE                  ,
            CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.CUST_ACCOUNT_ID),
            cur.STATUS                         ,
            cur.COLLECTOR_ID                   ,
            cur.CREDIT_ANALYST_ID              ,
            cur.CREDIT_CHECKING                ,
            cur.NEXT_CREDIT_REVIEW_DATE        ,
            cur.TOLERANCE                      ,
            cur.DISCOUNT_TERMS                 ,
            cur.DUNNING_LETTERS                ,
            cur.INTEREST_CHARGES               ,
            cur.PREF_FUNCTIONAL_CURRENCY       ,
            cur.SEND_STATEMENTS                ,
            cur.CREDIT_BALANCE_STATEMENTS      ,
            cur.CREDIT_HOLD                    ,
            cur.PROFILE_CLASS_ID               ,
            CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.SITE_USE_ID),
            cur.CREDIT_RATING                  ,
            cur.RISK_CODE                      ,
            cur.STANDARD_TERMS                 ,
            cur.OVERRIDE_TERMS                 ,
            cur.DUNNING_LETTER_SET_ID          ,
            cur.INTEREST_PERIOD_DAYS           ,
            cur.PAYMENT_GRACE_DAYS             ,
            cur.DISCOUNT_GRACE_DAYS            ,
            cur.STATEMENT_CYCLE_ID             ,
            cur.ACCOUNT_STATUS                 ,
            cur.PERCENT_COLLECTABLE            ,
            cur.AUTOCASH_HIERARCHY_ID          ,
            cur.ATTRIBUTE_CATEGORY             ,
            cur.ATTRIBUTE1                     ,
            cur.ATTRIBUTE2                     ,
            cur.ATTRIBUTE3                     ,
            cur.ATTRIBUTE4                     ,
            cur.ATTRIBUTE5                     ,
            cur.ATTRIBUTE6                     ,
            cur.ATTRIBUTE7                     ,
            cur.ATTRIBUTE8                     ,
            cur.ATTRIBUTE9                     ,
            cur.ATTRIBUTE10                    ,
            cur.PROGRAM_APPLICATION_ID         ,
            cur.PROGRAM_ID                     ,
            cur.PROGRAM_UPDATE_DATE            ,
            cur.REQUEST_ID                     ,
            cur.WH_UPDATE_DATE                 ,
            cur.ATTRIBUTE11                    ,
            cur.ATTRIBUTE12                    ,
            cur.ATTRIBUTE13                    ,
            cur.ATTRIBUTE14                    ,
            cur.ATTRIBUTE15                    ,
            cur.AUTO_REC_INCL_DISPUTED_FLAG    ,
            cur.TAX_PRINTING_OPTION            ,
            cur.CHARGE_ON_FINANCE_CHARGE_FLAG  ,
            cur.GROUPING_RULE_ID               ,
            cur.CLEARING_DAYS                  ,
            cur.JGZZ_ATTRIBUTE_CATEGORY        ,
            cur.JGZZ_ATTRIBUTE1                ,
            cur.JGZZ_ATTRIBUTE2                ,
            cur.JGZZ_ATTRIBUTE3                ,
            cur.JGZZ_ATTRIBUTE4                ,
            cur.JGZZ_ATTRIBUTE5                ,
            cur.JGZZ_ATTRIBUTE6                ,
            cur.JGZZ_ATTRIBUTE7                ,
            cur.JGZZ_ATTRIBUTE8                ,
            cur.JGZZ_ATTRIBUTE9                ,
            cur.JGZZ_ATTRIBUTE10               ,
            cur.JGZZ_ATTRIBUTE11               ,
            cur.JGZZ_ATTRIBUTE12               ,
            cur.JGZZ_ATTRIBUTE13               ,
            cur.JGZZ_ATTRIBUTE14               ,
            cur.JGZZ_ATTRIBUTE15               ,
            cur.GLOBAL_ATTRIBUTE1              ,
            cur.GLOBAL_ATTRIBUTE2              ,
            cur.GLOBAL_ATTRIBUTE3              ,
            cur.GLOBAL_ATTRIBUTE4              ,
            cur.GLOBAL_ATTRIBUTE5              ,
            cur.GLOBAL_ATTRIBUTE6              ,
            cur.GLOBAL_ATTRIBUTE7              ,
            cur.GLOBAL_ATTRIBUTE8              ,
            cur.GLOBAL_ATTRIBUTE9              ,
            cur.GLOBAL_ATTRIBUTE10             ,
            cur.GLOBAL_ATTRIBUTE11             ,
            cur.GLOBAL_ATTRIBUTE12             ,
            cur.GLOBAL_ATTRIBUTE13             ,
            cur.GLOBAL_ATTRIBUTE14             ,
            cur.GLOBAL_ATTRIBUTE15             ,
            cur.GLOBAL_ATTRIBUTE16             ,
            cur.GLOBAL_ATTRIBUTE17             ,
            cur.GLOBAL_ATTRIBUTE18             ,
            cur.GLOBAL_ATTRIBUTE19             ,
            cur.GLOBAL_ATTRIBUTE20             ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY      ,
            cur.CONS_INV_FLAG                  ,
            cur.CONS_INV_TYPE                  ,
            cur.AUTOCASH_HIERARCHY_ID_FOR_ADR  ,
            cur.LOCKBOX_MATCHING_OPTION        ,
            cur.OBJECT_VERSION_NUMBER          ,
            cur.CREATED_BY_MODULE              ,
            cur.APPLICATION_ID                 ,
            cur.REVIEW_CYCLE                   ,
            cur.LAST_CREDIT_REVIEW_DATE        ,
            CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.PARTY_ID),
            cur.CREDIT_CLASSIFICATION                    
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_PROFILE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
